package com.carbeauty.repository;

import com.carbeauty.entity.User;
import com.carbeauty.common.enums.UserRole;
import com.carbeauty.common.enums.BusinessStatus;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.util.List;
import java.util.Optional;

/**
 * 用户数据访问接口
 * 
 * 提供用户相关的数据库操作方法:
 * - 基础CRUD操作
 * - 按条件查询用户
 * - 用户统计查询
 * - 自定义查询方法
 * 
 * @author CarBeauty Team
 */
@Repository
public interface UserRepository extends JpaRepository<User, Long> {
    
    // ==================== 基础查询方法 ====================
    
    /**
     * 根据用户名查找用户
     * 用于登录验证
     */
    Optional<User> findByUsername(String username);
    
    /**
     * 根据用户名查找用户（忽略大小写）
     */
    Optional<User> findByUsernameIgnoreCase(String username);
    
    /**
     * 根据手机号查找用户
     */
    Optional<User> findByPhone(String phone);
    
    /**
     * 根据邮箱查找用户
     */
    Optional<User> findByEmail(String email);
    
    /**
     * 检查用户名是否存在
     */
    boolean existsByUsername(String username);
    
    /**
     * 检查手机号是否存在
     */
    boolean existsByPhone(String phone);
    
    /**
     * 检查邮箱是否存在
     */
    boolean existsByEmail(String email);
    
    // ==================== 按角色查询 ====================
    
    /**
     * 根据角色查找用户
     */
    List<User> findByRole(UserRole role);
    
    /**
     * 根据角色和状态查找用户
     */
    List<User> findByRoleAndStatus(UserRole role, BusinessStatus status);
    
    /**
     * 查找所有活跃的管理员
     */
    @Query("SELECT u FROM User u WHERE u.role = 'ADMIN' AND u.status = 'ACTIVE'")
    List<User> findActiveAdmins();
    
    /**
     * 查找所有活跃的员工（包括管理员、店长、员工）
     */
    @Query("SELECT u FROM User u WHERE u.role IN ('ADMIN', 'MANAGER', 'EMPLOYEE') AND u.status = 'ACTIVE'")
    List<User> findActiveEmployees();
    
    /**
     * 查找所有活跃的客户
     */
    @Query("SELECT u FROM User u WHERE u.role = 'CUSTOMER' AND u.status = 'ACTIVE'")
    List<User> findActiveCustomers();
    
    // ==================== 按状态查询 ====================
    
    /**
     * 根据状态查找用户
     */
    List<User> findByStatus(BusinessStatus status);
    
    /**
     * 查找所有活跃用户（按创建时间倒序）
     */
    List<User> findByStatusOrderByCreateTimeDesc(BusinessStatus status);
    
    // ==================== 模糊查询 ====================
    
    /**
     * 根据真实姓名模糊查询
     */
    List<User> findByRealNameContaining(String realName);
    
    /**
     * 根据用户名或真实姓名模糊查询
     */
    @Query("SELECT u FROM User u WHERE u.username LIKE %:keyword% OR u.realName LIKE %:keyword%")
    List<User> findByUsernameOrRealNameContaining(@Param("keyword") String keyword);
    
    /**
     * 多条件模糊查询
     */
    @Query("SELECT u FROM User u WHERE " +
           "(:username IS NULL OR u.username LIKE %:username%) AND " +
           "(:realName IS NULL OR u.realName LIKE %:realName%) AND " +
           "(:phone IS NULL OR u.phone LIKE %:phone%) AND " +
           "(:role IS NULL OR u.role = :role) AND " +
           "(:status IS NULL OR u.status = :status)")
    List<User> findByConditions(@Param("username") String username,
                               @Param("realName") String realName,
                               @Param("phone") String phone,
                               @Param("role") UserRole role,
                               @Param("status") BusinessStatus status);
    
    // ==================== 统计查询 ====================
    
    /**
     * 统计各角色用户数量
     */
    @Query("SELECT u.role, COUNT(u) FROM User u WHERE u.status = 'ACTIVE' GROUP BY u.role")
    List<Object[]> countByRole();
    
    /**
     * 统计活跃用户数量
     */
    long countByStatus(BusinessStatus status);
    
    /**
     * 统计指定角色的活跃用户数量
     */
    long countByRoleAndStatus(UserRole role, BusinessStatus status);
    
    /**
     * 统计今日新增用户数量
     */
    @Query("SELECT COUNT(u) FROM User u WHERE DATE(u.createTime) = CURRENT_DATE")
    long countTodayNewUsers();
    
    /**
     * 统计本月新增用户数量
     */
    @Query("SELECT COUNT(u) FROM User u WHERE YEAR(u.createTime) = YEAR(CURRENT_DATE) AND MONTH(u.createTime) = MONTH(CURRENT_DATE)")
    long countThisMonthNewUsers();
    
    // ==================== 客户相关查询 ====================
    
    /**
     * 查找余额大于指定金额的客户
     */
    @Query("SELECT u FROM User u WHERE u.role = 'CUSTOMER' AND u.balance > :minBalance AND u.status = 'ACTIVE'")
    List<User> findCustomersWithBalanceGreaterThan(@Param("minBalance") java.math.BigDecimal minBalance);
    
    /**
     * 查找积分大于指定数量的客户
     */
    @Query("SELECT u FROM User u WHERE u.role = 'CUSTOMER' AND u.points > :minPoints AND u.status = 'ACTIVE'")
    List<User> findCustomersWithPointsGreaterThan(@Param("minPoints") Integer minPoints);
    
    /**
     * 根据会员等级查找客户
     */
    @Query("SELECT u FROM User u WHERE u.role = 'CUSTOMER' AND u.memberLevel = :memberLevel AND u.status = 'ACTIVE'")
    List<User> findCustomersByMemberLevel(@Param("memberLevel") String memberLevel);
    
    /**
     * 查找首次登录的用户
     */
    List<User> findByIsFirstLoginTrueAndStatus(BusinessStatus status);
    
    /**
     * 查找长时间未登录的用户（超过指定天数）
     */
    @Query("SELECT u FROM User u WHERE u.lastLoginTime < :cutoffDate AND u.status = 'ACTIVE'")
    List<User> findInactiveUsers(@Param("cutoffDate") java.time.LocalDateTime cutoffDate);
    
    // ==================== 排序查询 ====================
    
    /**
     * 按创建时间倒序查找用户（分页）
     */
    org.springframework.data.domain.Page<User> findByStatusOrderByCreateTimeDesc(BusinessStatus status, org.springframework.data.domain.Pageable pageable);
    
    /**
     * 按最后登录时间倒序查找用户
     */
    List<User> findByStatusOrderByLastLoginTimeDesc(BusinessStatus status);
    
    /**
     * 按余额倒序查找客户
     */
    @Query("SELECT u FROM User u WHERE u.role = 'CUSTOMER' AND u.status = 'ACTIVE' ORDER BY u.balance DESC")
    List<User> findCustomersOrderByBalanceDesc();
    
    /**
     * 按积分倒序查找客户
     */
    @Query("SELECT u FROM User u WHERE u.role = 'CUSTOMER' AND u.status = 'ACTIVE' ORDER BY u.points DESC")
    List<User> findCustomersOrderByPointsDesc();
}
